library(readxl)

###############
#Import dataset
###############

facebook_data <- read_xlsx("social_media_ads_data.xlsx")


###############
#Ad performance
###############

#Unique user accounts reached
aggregate(facebook_data$performance_reach, list(group = facebook_data$group, 
                                                country = facebook_data$country), FUN = mean)

#Unique link clicks
aggregate(facebook_data$performance_clicks, list(group = facebook_data$group, 
                                                 country = facebook_data$country), FUN = mean)

#Survey starts
aggregate(facebook_data$performance_starts, list(group = facebook_data$group, 
                                                 country = facebook_data$country), FUN = mean)

#Survey completions
aggregate(facebook_data$performance_completions, list(group = facebook_data$group, 
                                                      country = facebook_data$country), FUN = mean)


##################
#Recruitment costs
##################

#Costs total
aggregate(facebook_data$costs_total, list(group = facebook_data$group, 
                                          country = facebook_data$country), FUN = mean)

#Costs per unique user account reached
aggregate(facebook_data$costs_reach, list(group = facebook_data$group, 
                                          country = facebook_data$country), FUN = mean)

#Costs per unique link click
aggregate(facebook_data$costs_click, list(group = facebook_data$group, 
                                          country = facebook_data$country), FUN = mean)

#Costs per survey start
aggregate(facebook_data$costs_start, list(group = facebook_data$group, 
                                          country = facebook_data$country), FUN = mean)

#Costs per survey completion
aggregate(facebook_data$costs_completion, list(group = facebook_data$group, 
                                               country = facebook_data$country), FUN = mean, na.rm = T)

#Costs per survey start (US - loosely related) without outliers
mean(facebook_data$costs_start[which(facebook_data$group == "loose" & 
                                       facebook_data$country == "USA" &
                                       (facebook_data$ad_id != 8 & facebook_data$ad_id != 21))])

#Costs per survey completion (US - loosely related) without outliers
mean(facebook_data$costs_completion[which(facebook_data$group == "loose" & 
                                            facebook_data$country == "USA" &
                                            (facebook_data$ad_id != 8 & facebook_data$ad_id != 21))])


#################
#User engagements
#################

#Number of likes
aggregate(facebook_data$engagements_likes, list(group = facebook_data$group, 
                                                country = facebook_data$country), FUN = mean)

#Number of reactions
aggregate(facebook_data$engagements_reactions, list(group = facebook_data$group, 
                                                    country = facebook_data$country), FUN = mean)

#Number of shares
aggregate(facebook_data$engagements_shares, list(group = facebook_data$group, 
                                                 country = facebook_data$country), FUN = mean)

#Number of saves
aggregate(facebook_data$engagements_saves, list(group = facebook_data$group, 
                                                country = facebook_data$country), FUN = mean)

#Number of comments
aggregate(facebook_data$engagements_comments, list(group = facebook_data$group, 
                                                   country = facebook_data$country), FUN = mean)

#Positive survey reactions
aggregate(facebook_data$engagements_positive_survey_reactions, list(group = facebook_data$group, 
                                                                    country = facebook_data$country), FUN = mean)

#Negative survey reactions
aggregate(facebook_data$engagements_negative_survey_reactions, list(group = facebook_data$group, 
                                                                    country = facebook_data$country), FUN = mean)

#Topic reactions
aggregate(facebook_data$engagements_topic_reactions, list(group = facebook_data$group, 
                                                          country = facebook_data$country), FUN = mean)

#Offensive content
aggregate(facebook_data$engagements_offensive_content, list(group = facebook_data$group, 
                                                            country = facebook_data$country), FUN = mean)

#Unrelated content and nonsense
aggregate(facebook_data$engagements_unrelated_nonsense, list(group = facebook_data$group, 
                                                             country = facebook_data$country), FUN = mean)

#Images and GIFs
aggregate(facebook_data$engagements_images_gifs, list(group = facebook_data$group, 
                                                      country = facebook_data$country), FUN = mean)


#######################
#Sample characteristics
#######################

#Germany
facebook_data_GER <- subset(facebook_data, country == "GER")

sum(facebook_data_GER$performance_completions) #N

weighted.mean(facebook_data_GER$female, facebook_data_GER$performance_completions, na.rm = T) #female

weighted.mean(facebook_data_GER$age, facebook_data_GER$performance_completions, na.rm = T) #age

weighted.mean(facebook_data_GER$tertiary_education, facebook_data_GER$performance_completions, na.rm = T) #tertiary education

weighted.mean(facebook_data_GER$secondary_education, facebook_data_GER$performance_completions, na.rm = T) #secondary education

weighted.mean(facebook_data_GER$mobile_device, facebook_data_GER$performance_completions, na.rm = T) #mobile device participation


#USA
facebook_data_USA <- subset(facebook_data, country == "USA")

sum(facebook_data_USA$performance_completions) #N

weighted.mean(facebook_data_USA$female, facebook_data_USA$performance_completions, na.rm = T) #female

weighted.mean(facebook_data_USA$age, facebook_data_USA$performance_completions, na.rm = T) #age

weighted.mean(facebook_data_USA$tertiary_education, facebook_data_USA$performance_completions, na.rm = T) #tertiary education

weighted.mean(facebook_data_USA$secondary_education, facebook_data_USA$performance_completions, na.rm = T) #secondary education

weighted.mean(facebook_data_USA$mobile_device, facebook_data_USA$performance_completions, na.rm = T) #mobile device participation
